﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace syc.Store
{
    public partial class 原料明细账 : System.Web.UI.Page
    {
        string sql1 =
@"declare @结存 numeric(18,3)
declare @结存金额 numeric(27,2)
select @结存=isnull(SUM(重量),0),@结存金额=isnull(SUM(单价*重量),0)
from 原料库存";

        string sql2 =
@"SELECT top 1000
原材料明细账.ID
,原材料明细账.日期
,原材料明细账.数量
,原材料明细账.单据ID
,case
when 原材料明细账.数量>0 then
	原料供货单位.名称
else
	case 
	when 原材料明细账.使用单位ID=1  then
		'一分厂'
	when 原材料明细账.使用单位ID=2  then
		'二分厂'
	when 原材料明细账.使用单位ID=3  then
		'三分厂'
	when 原材料明细账.使用单位ID=4  then
		'四分厂'
	when 原材料明细账.使用单位ID=5  then
		'五分厂'
	else
		'其他部门'
	end
end as 供货或使用
,原材料明细账.单价
,case
when 原材料明细账.数量>0 then
	原材料明细账.重量
else
	null
end as 收入数量

,case
when 原材料明细账.供货单位ID>0 then
	null
else
	-原材料明细账.重量
end as 支出数量
,@结存 as 结存数量
,@结存金额 as 结存金额
FROM 原材料明细账
left outer join 原料 on 原材料明细账.原料ID=原料.ID
left outer join 原料供货单位 on 原材料明细账.供货单位ID=原料供货单位.ID";

        string order = " order by 原材料明细账.日期 asc,原材料明细账.ID asc";

        decimal jc = 0, jcje = 0;

        decimal[] hj = { 0, 0, 0, 0 };
        protected void Page_Load(object sender, EventArgs e)
        {

            if (!this.IsPostBack)
            {
                this.GuiGe.DataBind();
                this.YuanLiao.DataBind();
                this.Date1.Text = this.Date2.Text = DateTime.Today.ToLongDateString();
            }
            else
            {
                this.SqlDataSource1.SelectCommand = this.SqlText.Text;
            }
            this.Message.Text = "·准备就绪";

        }

        protected void Button_Query_Click(object sender, EventArgs e)
        {
            int ylid;
            DateTime d1, d2;
            if (!int.TryParse(this.YuanLiao.Text, out ylid))
            {
                return;
            }
            if(!DateTime.TryParse(this.Date1.Text,out d1))
            {
                this.Message.Text = "·“起始日期”录入有误";
                return;
            }
            if(!DateTime.TryParse(this.Date2.Text,out d2))
            {
                this.Message.Text = "·“截止日期”录入有误";
                return;
            }
            this.SqlText.Text = sql1
                + " where 原料库存.原料ID=" + ylid + " and 原料库存.日期<'" + d1.ToShortDateString() + "'\n"
                +sql2
                + " where 原材料明细账.原料ID=" + ylid + " and 原材料明细账.日期>='" + d1.ToShortDateString() + "' and 原材料明细账.日期<='" + d2.ToShortDateString() + "'"
                +order;
            //Response.Write(this.SqlText.Text);
            //Response.End();

            this.SqlDataSource1.SelectCommand = "";
            this.SqlDataSource1.SelectCommand = this.SqlText.Text;
            this.Button_Export.Enabled = false;
            this.Button_View.Enabled = false;
            this.GridView1.SelectedIndex = -1;
        }


        protected void GridView1_DataBound(object sender, EventArgs e)
        {
            this.TextBox_Pager.Text = (this.GridView1.PageIndex + 1).ToString();
            this.Label_Pages.Text = " 共" + this.GridView1.PageCount.ToString() + "页 ";

            this.Button_Pager_First.CssClass = "pageturn-first-b";
            this.Button_Pager_First.Enabled = true;
            this.Button_Pager_Prev.CssClass = "pageturn-prev-b";
            this.Button_Pager_Prev.Enabled = true;
            this.Button_Pager_Next.CssClass = "pageturn-next-b";
            this.Button_Pager_Next.Enabled = true;
            this.Button_Pager_Last.CssClass = "pageturn-last-b";
            this.Button_Pager_Last.Enabled = true;
            if (this.GridView1.PageIndex == 0)
            {
                this.Button_Pager_Prev.CssClass = "pageturn-prev-g";
                this.Button_Pager_Prev.Enabled = false;
            }
            if (this.GridView1.PageIndex < 2)
            {
                this.Button_Pager_First.CssClass = "pageturn-first-g";
                this.Button_Pager_First.Enabled = false;
            }
            if (this.GridView1.PageIndex == this.GridView1.PageCount - 1 || this.GridView1.PageCount == 0)
            {
                this.Button_Pager_Next.CssClass = "pageturn-next-g";
                this.Button_Pager_Next.Enabled = false;

            }
            if (this.GridView1.PageIndex > this.GridView1.PageCount - 3)
            {
                this.Button_Pager_Last.CssClass = "pageturn-last-g";
                this.Button_Pager_Last.Enabled = false;
            }
            if (hj[0] > 0 || hj[2] > 0)
            {
                ((Label)this.GridView1.FooterRow.FindControl("Label22")).Text = hj[0].ToString();
                ((Label)this.GridView1.FooterRow.FindControl("Label23")).Text = hj[1].ToString("c");
                ((Label)this.GridView1.FooterRow.FindControl("Label24")).Text = hj[2].ToString();
                ((Label)this.GridView1.FooterRow.FindControl("Label25")).Text = hj[3].ToString("c");
            }
        }

        protected void GridView1_PageIndexChanged(object sender, EventArgs e)
        {
            this.GridView1_DataBound(null, null);
        }

        protected void Pager_Click(object sender, EventArgs e)
        {
            if (sender.GetType().ToString() == "System.Web.UI.WebControls.Button")
            {
                switch (((Button)sender).CommandName)
                {
                    case "--":
                        this.GridView1.PageIndex = 0;
                        break;
                    case "-":
                        if (this.GridView1.PageIndex > 0) this.GridView1.PageIndex--;
                        break;
                    case "+":
                        if (this.GridView1.PageIndex < this.GridView1.PageCount - 1) this.GridView1.PageIndex++;
                        break;
                    case "++":
                        this.GridView1.PageIndex = this.GridView1.PageCount - 1;
                        break;
                }
            }
            else
            {

                int p;
                if (int.TryParse(TextBox_Pager.Text, out p))
                {
                    if (p >= 1 && p <= this.GridView1.PageCount)
                    {
                        this.GridView1.PageIndex = p - 1;
                    }
                }
                TextBox_Pager.Text = (this.GridView1.PageIndex + 1).ToString();
            }

        }

        protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e)
        {

            Label_Total.Text = "共查询到 " + e.AffectedRows + " 项记录";
            if (e.AffectedRows > 0)
            {
                this.Button_Export.Enabled = true;
            }
        }

        protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.SelectedID.Text = this.GridView1.SelectedDataKey[0].ToString();
            this.Button_View.Enabled = true;
            //Response.Write(this.GridView1.SelectedDataKey[1]);
            //Response.End();
            if ((int)this.GridView1.SelectedDataKey[1] >0)
            {
                this.Button_View.OnClientClick = "parent.swin_load('/Store/原料入库单.aspx?id=" + (int)this.GridView1.SelectedDataKey[2] + "');";
            }
            else
            {
                this.Button_View.OnClientClick = "parent.swin_load('/Store/原料出库单.aspx?id=" + (int)this.GridView1.SelectedDataKey[2] + "');";
            }

        }

        protected void Button_Export_Click(object sender, EventArgs e)
        {
            string part1 =
@"<html xmlns:o='urn:schemas-microsoft-com:office:office'
xmlns:x='urn:schemas-microsoft-com:office:excel'
xmlns='http://www.w3.org/TR/REC-html40'>
<head>
<meta http-equiv=Content-Type content='text/html; charset=utf-8'>
<style>
<!--table
@page
{mso-header-data:;
mso-footer-data:;
margin:.79in .31in .79in .31in;
mso-header-margin:.51in;
mso-footer-margin:.51in;
mso-page-orientation:landscape;
mso-horizontal-page-align:center;
}
-->
</style>
<!--[if gte mso 9]><xml>
 <x:ExcelWorkbook>
  <x:ExcelWorksheets>
   <x:ExcelWorksheet>
    <x:Name>原材料明细账</x:Name>
    <x:WorksheetOptions>
     <x:DefaultRowHeight>285</x:DefaultRowHeight>
     <x:Print>
      <x:ValidPrinterInfo/>
      <x:Scale>100</x:Scale>
      <x:PaperSizeIndex>9</x:PaperSizeIndex>
      <x:HorizontalResolution>200</x:HorizontalResolution>
      <x:VerticalResolution>200</x:VerticalResolution>
     </x:Print>
     <x:Selected/>
     <x:Panes>
      <x:Pane>
       <x:Number>3</x:Number>
       <x:ActiveRow>0</x:ActiveRow>
       <x:ActiveCol>0</x:ActiveCol>
      </x:Pane>
     </x:Panes>
    </x:WorksheetOptions>
   </x:ExcelWorksheet>
 </x:ExcelWorkbook>
</xml><![endif]-->
</head>
<body>";
            string part2 =
@"<style>
tr{height:24px;}
td{text-align:center;font-size:10pt;}
td.s1{border:.5pt solid #000000;border-top:1pt solid #000000;border-left:1pt solid #000000;}
td.s2{border:.5pt solid #000000;border-top:1pt solid #000000;}
td.s3{border:.5pt solid #000000;border-top:1pt solid #000000;border-right:1pt solid #000000;}
td.s4{border:.5pt solid #000000;border-left:1pt solid #000000;}
td.s5{border:.5pt solid #000000;}
td.s6{border:.5pt solid #000000;border-right:1pt solid #000000;}
td.s7{border:.5pt solid #000000;border-bottom:1pt solid #000000;border-left:1pt solid #000000;}
td.s8{border:.5pt solid #000000;border-bottom:1pt solid #000000;}
td.s9{border:.5pt solid #000000;border-bottom:1pt solid #000000;border-right:1pt solid #000000;}
</style>
<table border=0 cellpadding=0 style=border-collapse:collapse>
<tr>
<td height=40 colspan=10 style=font-size:20pt;><b><u>&nbsp;原 材 料 明 细 账&nbsp;</u></b></td>
</tr>";
            string part3 =
@"<tr>
<td colspan=2 style=text-align:left;>物质名称：_</td>
<td colspan=3 style=text-align:left;>钢级：J55</td>
<td colspan=2 style=text-align:left;>规格：_</td>
<td colspan=2 style=text-align:left;>计量单位：吨</td>
<td></td>
</tr>";
            string part4 =
@"<tr>
<td class=s1 width=70 rowspan=2>日期</td>
<td class=s2 width=120 rowspan=2>供货或使用</td>
<td class=s2 width=90 rowspan=2>单价(元)</td>
<td class=s2 colspan=2>收 入</td>
<td class=s2 colspan=2>支 出</td>
<td class=s2 colspan=2>结 存</td>
<td class=s3 width=70 rowspan=2>稽核人员<br>盖章</td>
</tr>
<tr>
<td class=s5 width=70>数 量(吨)</td>
<td class=s5 width=110>金 额(元)</td>
<td class=s5 width=70>数 量(吨)</td>
<td class=s5 width=110>金 额(元)</td>
<td class=s5 width=70>数 量(吨)</td>
<td class=s5 width=110>金 额(元)</td>
</tr>";
            string part5 =
@"<tr>
<td style=text-align:right>记账员：</td>
<td colspan=6 style=text-align:left>　</td>
<td style=text-align:right>保管员：</td>
<td colspan=2 style=text-align:left>　</td>
</tr>
</table>
</body>
</html>";


            Response.Charset = "utf-8";
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            Response.ContentType = "application/ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=\"" + Server.UrlEncode("原料明细账") + ".xls\"");

            part3 = part3.Replace("物质名称：_", "物质名称：" + this.YuanLiao.SelectedItem.Text);
            part3 = part3.Replace("规格：_", "规格：" + this.GuiGe.Items[this.YuanLiao.SelectedIndex].Text);
            Response.Write(part1);
            Response.Write(part2);
            Response.Write(part3);
            Response.Write(part4);

            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            Cmd.CommandText = this.SqlText.Text;

            Cmd.CommandType = CommandType.Text;
            SqlDataReader Reader = Cmd.ExecuteReader();

            int n = 0;
            decimal sl = 0;
            decimal dj;
            while (Reader.Read())
            {
                if (n == 0)
                {
                    jc = (decimal)Reader["结存数量"];
                    jcje = (decimal)Reader["结存金额"];
                }
                dj = (decimal)Reader["单价"];
                
                //tmp = Reader["收入数量"];
                
                Response.Write("<tr><td class=s4>"+((DateTime)Reader["日期"]).ToShortDateString()+"</td>");
                Response.Write("<td class=s5>" + (string)Reader["供货或使用"] + "</td>");
                Response.Write("<td class=s5>" + dj + "</td>");
                if (Convert.IsDBNull(Reader["收入数量"]))
                {
                    Response.Write("<td class=s5></td>");
                    Response.Write("<td class=s5></td>");
                    sl = (decimal)Reader["支出数量"];
                    Response.Write("<td class=s5 style=text-align:right>" + (sl) + "</td>");
                    Response.Write("<td class=s5 style=text-align:right>" + (sl * dj).ToString("c") + "</td>");
                    jc -= sl;
                    jcje -= (sl * dj);
                    hj[2] += sl;
                    hj[3] += (sl * dj);
                }
                else
                {
                    sl = (decimal)Reader["收入数量"];
                    Response.Write("<td class=s5 style=text-align:right>" + sl + "</td>");
                    Response.Write("<td class=s5 style=text-align:right>" + (sl * dj).ToString("c") + "</td>");
                    Response.Write("<td class=s5></td>");
                    Response.Write("<td class=s5></td>");
                    jc += sl;
                    jcje += (sl * dj);
                    hj[0] += sl;
                    hj[1] += (sl * dj);
                }
                Response.Write("<td class=s5 style=text-align:right>" + jc + "</td>");
                Response.Write("<td class=s5 style=text-align:right>" + jcje.ToString("c") + "</td>");
                Response.Write("<td class=s6></td></tr>");
                n++;
            }
            Response.Write("<tr><td class=s7 colspan=2></td><td class=s8>合计：</td>");
            Response.Write("<td class=s8 style=text-align:right>" + hj[0] + "</td>");
            Response.Write("<td class=s8 style=text-align:right>" + hj[1].ToString("c") + "</td>");
            Response.Write("<td class=s8 style=text-align:right>" + hj[2] + "</td>");
            Response.Write("<td class=s8 style=text-align:right>" + hj[3].ToString("c") + "</td>");
            Response.Write("<td class=s9 colspan=3></td></tr>");

            Reader.Close();
            Conn.Close();
            Reader.Dispose();
            Cmd.Dispose();
            Conn.Dispose();

            Response.Write(part5);
            Response.End();
        }

        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.DataItemIndex > -1)
            {
                string dj,sl;
                dj=((Label)e.Row.FindControl("Label5")).Text;
                sl = ((Label)e.Row.FindControl("Label6")).Text;
                decimal je;
                if (sl != "")
                {
                    je = decimal.Parse(dj) * decimal.Parse(sl);
                    ((Label)e.Row.FindControl("Label7")).Text = je.ToString("c");
                }
                else
                {
                    sl = ((Label)e.Row.FindControl("Label8")).Text;
                    je = decimal.Parse(dj) * decimal.Parse(sl);
                    ((Label)e.Row.FindControl("Label9")).Text = je.ToString("c");
                    je = -je;
                }
                if (e.Row.DataItemIndex == 0)
                {
                    jc = decimal.Parse(((Label)e.Row.FindControl("Label10")).Text);
                    jcje = decimal.Parse(((Label)e.Row.FindControl("Label11")).Text);
                    //Response.Write(jc + " " + jcje+"<br>"+this.SqlText.Text);
                }

                jc += decimal.Parse(sl)*(Math.Abs(je)/je);
                jcje += je;
                ((Label)e.Row.FindControl("Label10")).Text = jc.ToString();
                ((Label)e.Row.FindControl("Label11")).Text = jcje.ToString("c");
                if (je >= 0)
                {
                    hj[0] += decimal.Parse(sl);
                    hj[1] += je;
                }
                else
                {
                    hj[2] += decimal.Parse(sl);
                    hj[3] -= je;
                }
            }
        }



    }
}
